Imports System.Windows.Forms

Public Class frmConvertRecords

    Private TableName As String

    Friend Sub New(ByVal _TableName As String)
        InitializeComponent()
        TableName = _TableName
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        If RunSQL() Then RefreshAllForms()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub frmChangeRecords_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            With dgv
                .Columns.Add("Field_Name", "Field Name")
                Dim dgvCbo As New DataGridViewComboBoxColumn
                With dgvCbo
                    .Name = "ConvGrp"
                    .HeaderText = "Conversion Group"
                    For Each s As String In Project.DB.GetRecordList("ConvGrp", "ConvGroup")
                        .Items.Add(s)
                    Next
                    .Items.Add("")
                    .DisplayStyle = DataGridViewComboBoxDisplayStyle.ComboBox
                    .Sorted = True
                End With
                .Columns.Add(dgvCbo)

                .RowHeadersVisible = False
                .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                For c As Integer = 0 To .ColumnCount - 1
                    With .Columns(c)
                        .AutoSizeMode = Choose(c + 1, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.Fill)
                        .ReadOnly = IIf(c = 0, True, False)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                    End With
                Next
                With .Columns(0)
                    .ReadOnly = True
                    .DefaultCellStyle.BackColor = SystemColors.ButtonFace
                End With
                For Each c As DataColumn In Project.GetTableTemplate(TableName).Columns
                    .Rows.Add(c.ColumnName, "")
                Next
                .CurrentCell = .Item(1, 0)
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function RunSQL() As Boolean
        Try
            Dim NumRec As Integer = RunQuery(True)
            If NumRec < 0 Then
                Return False
            ElseIf NumRec = 0 Then
                MessageBox.Show("No records match the criteria you specified.", Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Return False
            Else
                If MessageBox.Show(String.Format("You are about to change {0} records. This operation cannot be undone. Are you sure you want to continue?", CType(NumRec, Integer)), Me.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No Then Return False
            End If

            NumRec = RunQuery()

            IniFile.SetKey(clsWRDBIniFile.enumSection.Application, clsWRDBIniFile.enumKey.AppLastEditTime, Now.ToString(System.Globalization.CultureInfo.InvariantCulture.DateTimeFormat))

            If NumRec > 0 And Not MainWRDB.ExpertMode Then MessageBox.Show(String.Format("A total of {0} records were changed.", NumRec), Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)

            Return True

        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return False
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        Finally
            IniFile.Save()
        End Try
    End Function

    Private Function RunQuery(Optional ByVal GetCount As Boolean = False) As Integer
        Try
            Dim NumRec As Integer = 0
            For r As Integer = 0 To dgv.RowCount - 1
                Dim FieldName As String = dgv.Item(0, r).Value
                Dim ConvGroup As String = dgv.Item(1, r).Value
                If ConvGroup <> "" Then
                    Dim dictConv As New Generic.Dictionary(Of String, String)
                    Dim dr As DbDataReader = Project.DB.ExecuteReader("SELECT ConvFrom,ConvTo FROM ConvGrp WHERE ConvGroup='{0}'", ConvGroup)
                    While dr IsNot Nothing AndAlso dr.Read
                        Dim strFrom As String = Project.DB.TextString(TestNull(dr.GetString(0), ""))
                        Dim strTo As String = Project.DB.TextString(TestNull(dr.GetString(1), ""))
                        dictConv.Add(strFrom, strTo)
                    End While
                    dr.Close()
                    Dim i As Integer = 0
                    For Each kv As Generic.KeyValuePair(Of String, String) In dictConv
                        If Not MainForm.WriteProgress(i, dictConv.Count - 1, IIf(GetCount, "Getting record count...", "Converting records..."), ProgressBarStyle.Blocks, True) Then Return -1
                        Dim Num As Object = Nothing
                        If GetCount Then
                            Num = Project.DB.ExecuteScalar("SELECT COUNT(*) FROM {0} WHERE {1}={2}", ActiveBrowser.FullTableName, FieldName, kv.Key)
                        Else
                            Num = Project.DB.ExecuteNonQuery("UPDATE {0} SET {1}={2} WHERE {1}={3}", ActiveBrowser.FullTableName, FieldName, kv.Value, kv.Key)
                        End If
                        If Num Is Nothing OrElse Num < 0 Then Return -1
                        NumRec += Num
                        i += 1
                    Next
                End If
            Next
            Return NumRec
        Catch ex As DbSqlException
            WarningMsg(ex.Message)
            Return -1
        Catch ex As Exception
            ErrorMsg(, ex)
            Return -1
        Finally
            MainForm.ProgressComplete()
        End Try
    End Function

    Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellEnter
        If e.ColumnIndex = 0 Then Me.dgv.Item(1, e.RowIndex).Selected = True
    End Sub

    ''' <summary>
    ''' Utility function to easily add a column to a data table
    ''' </summary>
    ''' <param name="ColumnName">Name of field</param>
    ''' <param name="Caption">Caption for grid heading</param>
    ''' <param name="DataType">Data type</param>
    ''' <param name="MaxLength">Maximum field length (for strings)</param>
    ''' <returns>Data table column</returns>
    Friend Function NewColumn(ByVal ColumnName As String, ByVal Caption As String, ByVal DataType As System.Type, Optional ByVal MaxLength As Integer = 0) As DataColumn
        Dim col As New DataColumn()
        With col
            .ColumnName = ColumnName
            .Caption = Caption
            .DataType = DataType
            If DataType Is GetType(String) Then .MaxLength = MaxLength
        End With
        Return col
    End Function
End Class
